

/*********************************************************************/
/*                                                                   */ 
/* Provide cutoffs at 1st and 99th percentiles for winsorizing data  */
/*                                                                   */ 
/*********************************************************************/



libname raw 'G:\Data\Rawdata\707677';

libname stats 'G:\Data\Rawdata\707677\Eksterne data'; 

libname common 'G:\Data\Workdata\707677\Common\01Data\01SAS';





/* Retrieve data from panel data set */



data win;

set common.panel;

keep pnr aar ERHVERVSINDK_real AKTIV_real PASSIV_real FORMUE_real KOEJD_real timelon_real PANTGAELD_real BANKGAELD_real OBLGAELD_real PRIGALD_real GALDIO_real mortgage_real;

run;





/* Find 1st and 99th percentiles by year */



proc summary data=win;

class aar;

output out=stats

p99(ERHVERVSINDK_real AKTIV_real PASSIV_real FORMUE_real KOEJD_real timelon_real PANTGAELD_real BANKGAELD_real OBLGAELD_real PRIGALD_real GALDIO_real mortgage_real)=

p99_earned p99_aktiv p99_passiv p99_formue p99_koejd p99_timelon p99_PANTGAELD p99_BANKGAELD p99_OBLGAELD p99_PRIGALD p99_GALDIO p99_mortgage


p1(ERHVERVSINDK_real AKTIV_real PASSIV_real FORMUE_real KOEJD_real timelon_real PANTGAELD_real BANKGAELD_real OBLGAELD_real PRIGALD_real GALDIO_real mortgage_real)=

p1_earned p1_aktiv p1_passiv p1_formue p1_koejd p1_timelon p1_PANTGAELD p1_BANKGAELD p1_OBLGAELD p1_PRIGALD p1_GALDIO p1_mortgage;

run;




data stats;

set stats;

drop _type_ _freq_;

if aar~=.;

run;






/* Save data on hard drive */


data common.winsorize;

set stats;

run;







/***********************************/
/* Winsorize variables in basedata */
/***********************************/



data list;

set common.list;

newaar=input(aar,4.0);

run;



data stats;

set stats;

rename aar=newaar;

run;



proc sql;

create table list as select *

from list as a left join

stats as b on

a.newaar=b.newaar;

quit;




data list;

set list;


if Erhvervsindk_13 < p1_earned and Erhvervsindk_13~=. then Erhvervsindk_13=p1_earned;

if Erhvervsindk_13 > p99_earned and Erhvervsindk_13~=. then Erhvervsindk_13=p99_earned;


if  AKTIV< p1_aktiv and AKTIV~=. then AKTIV=p1_aktiv;

if  AKTIV> p99_aktiv and AKTIV~=. then AKTIV=p99_aktiv;


if PASSIV < p1_passiv and PASSIV~=. then PASSIV=p1_passiv;

if PASSIV > p99_passiv and PASSIV~=. then PASSIV=p99_passiv;


if FORMUE < p1_formue and FORMUE~=. then FORMUE=p1_formue;

if FORMUE > p99_formue and FORMUE~=. then FORMUE=p99_formue;


if KOEJD < p1_koejd and KOEJD~=. then KOEJD=p1_koejd;

if KOEJD > p99_koejd and KOEJD~=. then KOEJD=p99_koejd;


if PANTGAELD  < p1_PANTGAELD  and PANTGAELD ~=. then PANTGAELD =p1_PANTGAELD;

if PANTGAELD > p99_PANTGAELD  and PANTGAELD ~=. then PANTGAELD =p99_PANTGAELD;


if BANKGAELD< p1_BANKGAELD and BANKGAELD~=. then BANKGAELD=p1_BANKGAELD;

if BANKGAELD > p99_BANKGAELD and BANKGAELD~=. then BANKGAELD=p99_BANKGAELD;


if OBLGAELD < p1_OBLGAELD  and OBLGAELD ~=. then OBLGAELD =p1_OBLGAELD;

if  OBLGAELD > p99_OBLGAELD  and OBLGAELD ~=. then OBLGAELD =p99_OBLGAELD;


if  PRIGALD< p1_PRIGALD and PRIGALD~=. then PRIGALD=p1_PRIGALD;

if  PRIGALD> p99_PRIGALD and PRIGALD~=. then PRIGALD=p99_PRIGALD;


if  GALDIO< p1_GALDIO and GALDIO~=. then GALDIO=p1_GALDIO;

if  GALDIO> p99_GALDIO and GALDIO~=. then GALDIO=p99_GALDIO;


if  mortgage< p1_mortgage and mortgage~=. then mortgage=p1_mortgage;

if  mortgage> p99_mortgage and mortgage~=. then mortgage=p99_mortgage;


run;




proc sort data=list;

by pnr;

run;



data list;

set list;

drop newaar 

p99_earned p99_aktiv p99_passiv p99_formue p99_koejd p99_timelon p99_PANTGAELD p99_BANKGAELD p99_OBLGAELD p99_PRIGALD p99_GALDIO p99_mortgage

p1_earned p1_aktiv p1_passiv p1_formue p1_koejd p1_timelon p1_PANTGAELD p1_BANKGAELD p1_OBLGAELD p1_PRIGALD p1_GALDIO p1_mortgage;

run;




data common.list;

set list;

run;



proc datasets library=work nolist;

delete list;

quit;





/*************************************/
/* Winsorize variables in panel data */
/*************************************/



data stats;

set stats;

rename newaar=aar;

run;



data panel;

set common.panel;

run;





proc sql;

create table panel as select *

from panel as a left join

stats as b on

a.aar=b.aar;

quit;




data panel;

set panel;

if erhvervsindk_real<p1_earned  and erhvervsindk_real~=. then erhvervsindk_real=p1_earned;

if erhvervsindk_real>p99_earned and erhvervsindk_real~=. then erhvervsindk_real=p99_earned;


if aktiv_real<p1_aktiv  and aktiv_real~=. then aktiv_real=p1_aktiv;

if aktiv_real>p99_aktiv and aktiv_real~=. then aktiv_real=p99_aktiv;


if passiv_real<p1_passiv  and passiv_real~=. then passiv_real=p1_passiv;

if passiv_real>p99_passiv and passiv_real~=. then passiv_real=p99_passiv;


if formue_real<p1_formue  and formue_real~=. then formue_real=p1_formue;

if formue_real>p99_formue and formue_real~=. then formue_real=p99_formue;


if koejd_real<p1_koejd  and koejd_real~=. then koejd_real=p1_koejd;

if koejd_real>p99_koejd and koejd_real~=. then koejd_real=p99_koejd;


if timelon_real < p1_timelon and timelon_real~=. then timelon_real=p1_timelon;

if timelon_real > p99_timelon and timelon_real~=. then timelon_real=p99_timelon;


if PANTGAELD_real  < p1_PANTGAELD  and PANTGAELD_real ~=. then PANTGAELD_real =p1_PANTGAELD;

if PANTGAELD_real > p99_PANTGAELD  and PANTGAELD_real ~=. then PANTGAELD_real =p99_PANTGAELD;


if BANKGAELD_real< p1_BANKGAELD and BANKGAELD_real~=. then BANKGAELD_real=p1_BANKGAELD;

if BANKGAELD_real > p99_BANKGAELD and BANKGAELD_real~=. then BANKGAELD_real=p99_BANKGAELD;


if OBLGAELD_real < p1_OBLGAELD  and OBLGAELD_real ~=. then OBLGAELD_real =p1_OBLGAELD;

if OBLGAELD_real > p99_OBLGAELD  and OBLGAELD_real ~=. then OBLGAELD_real =p99_OBLGAELD;


if  PRIGALD_real< p1_PRIGALD and PRIGALD_real~=. then PRIGALD_real=p1_PRIGALD;

if  PRIGALD_real> p99_PRIGALD and PRIGALD_real~=. then PRIGALD_real=p99_PRIGALD;


if  GALDIO_real< p1_GALDIO and GALDIO_real~=. then GALDIO_real=p1_GALDIO;

if  GALDIO_real> p99_GALDIO and GALDIO_real~=. then GALDIO_real=p99_GALDIO;


if  mortgage_real< p1_mortgage and mortgage_real~=. then mortgage_real=p1_mortgage;

if  mortgage_real> p99_mortgage and mortgage_real~=. then mortgage_real=p99_mortgage;


run;




proc sort data=panel;

by pnr aar;

run;



data panel;

set panel;

drop p99_earned p99_aktiv p99_passiv p99_formue p99_koejd p99_timelon p99_PANTGAELD p99_BANKGAELD p99_OBLGAELD p99_PRIGALD p99_GALDIO p99_mortgage

p1_earned p1_aktiv p1_passiv p1_formue p1_koejd p1_timelon p1_PANTGAELD p1_BANKGAELD p1_OBLGAELD p1_PRIGALD p1_GALDIO p1_mortgage;

run;



data common.panel;

set panel;

run;








proc datasets library=work kill nolist;

quit;







